SpdrParentReviewResponse

V8 Message Definiton

SpdrParentReviewResponse records are created when a parent order review request is completed

METADATA

Attribute	Value
Topic	4195-parent-review
MLink Token	SystemData
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
parentNumber	CHAR(19)	PRI	'0000-0000-0000-0000'	SPDR order number
sysEnvironment	enum - SysEnvironment		'None'	original source sys environment Stable Current etc
runStatus	enum - RunStatus		'None'	original source run status ProdBeta
parentShape	enum - SpdrOrderShape		'None'	Parent Shape Single Cross MLeg MLegCross
altOrderId	VARCHAR(24)		''	alternate order ID usually clOrdId from client
altPrevOrderId	VARCHAR(24)		''	alternate prev order ID usually origClOrdId from client during cxlreplace
altAccnt	VARCHAR(32)		''	alternate client assigned long account string optional used to map between client and SR account strings
altUserName	VARCHAR(24)		''	alternate client assigned user name optional used to map between client and SR account strings
secKey_at	enum - AssetType		'None'	Composite Security Key
secKey_ts	enum - TickerSrc		'None'	Composite Security Key
secKey_tk	VARCHAR(12)		''	Composite Security Key
secKey_yr	SMALLINT UNSIGNED		0	Composite Security Key
secKey_mn	TINYINT UNSIGNED		0	Composite Security Key
secKey_dy	TINYINT UNSIGNED		0	Composite Security Key
secKey_xx	DOUBLE		0	Composite Security Key
secKey_cp	enum - CallPut		'Call'	Composite Security Key
secType	enum - SpdrKeyType		'None'	Security Type Stock Future Option
securityDesc	TINYTEXT		''	additional security description
accnt	VARCHAR(16)		''	SR trading account
clientFirm	VARCHAR(16)		''	SR client firm
spdrSource	enum - SpdrSource		'None'	from original parent order
groupingCode	CHAR(19)		'0000-0000-0000-0000'	from original parent order
clientClOrdId	VARCHAR(24)		''	client ClOrdId if handled by ClientGateway
srcRoutingCode	VARCHAR(65)		''	RoutingCode if handled by ClientGateway
strategy	VARCHAR(36)		''	clientsupplied strategy stringvisible on SpiderRock GUI tools and other order reports
userName	VARCHAR(24)		''	name of the user entering the order
orderSide	enum - BuySell		'None'
orderSize	INT		0	maximum fill size contracts can be less than requested
orderActiveSize	INT		0
reviewResult	enum - ReviewResult		'None'
reviewText	TINYTEXT		''	review release reject text will be showed to the user submitting the parent order
responseSpdrSource	enum - SpdrSource		'None'	source of the response SRSE FIX etc
responseClOrdId	VARCHAR(24)		''	review request response clOrdID if any
responseOrigClOrdId	VARCHAR(24)		''	review request response orig clOrdID
responseCancelClOrdId	VARCHAR(24)		''	review request response cancel clOrdID
firmType	enum - FirmType		'None'	will override firmType on the original parent order if None
orderCapacity	enum - OrderCapacity		'None'	will override orderCapacity on the original parent order if None
positionType	enum - PositionType		'None'	will override positionType on the original parent order if None
ssaleFlag	enum - ShortSaleFlag		'None'	will override ssaleFlag on the original parent order if None
locateQuan	INT		0	locate quantity
locateFirm	VARCHAR(6)		''	locate firm
locatePool	VARCHAR(16)		''	locate pool firm granting the locate
userData1	TINYTEXT		''	will override value on original parent order if null
userData2	TINYTEXT		''	will override value on the original parent order if null
childData	TINYTEXT		''	will override value on the original parent order if null
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
OrderLegsList	JSON		'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
parentNumber	1

JSON Block (OrderLegsList)

Field	Type	Comment
ticker	TickerKey	stock ticker
stockSide	enum - stockSide	Buy Sell None
stockShares	int	number of shares included zero if none
ssaleFlag	enum - ssaleFlag	will override value on the original parent order if None
reviewStkLegId	string	client review stock leg Id
reviewStkLegAltId	string	client review stock leg alt Id
numLegs	byte	number of valid legs below
secKey1	OptionKey	leg 1
secType1	enum - secType1
mult1	ushort
side1	enum - side1
posType1	enum - posType1	will override value on the original parent order if None
ssaleFlag1	enum - ssaleFlag1	will override value on the original parent order if None
reviewLegId1	string	client review leg 1 Id
reviewLegAltId1	string	client review leg 1 alt Id
secKey2	OptionKey	leg 2
secType2	enum - secType2
mult2	ushort
side2	enum - side2
posType2	enum - posType2
ssaleFlag2	enum - ssaleFlag2
reviewLegId2	string	client review leg 2 Id
reviewLegAltId2	string	client review leg 2 alt Id
secKey3	OptionKey	leg 3
secType3	enum - secType3
mult3	ushort
side3	enum - side3
posType3	enum - posType3
ssaleFlag3	enum - ssaleFlag3
reviewLegId3	string	client review leg 3 Id
reviewLegAltId3	string	client review leg 3 alt Id
secKey4	OptionKey	leg 4
secType4	enum - secType4
mult4	ushort
side4	enum - side4
posType4	enum - posType4
ssaleFlag4	enum - ssaleFlag4
reviewLegId4	string	client review leg 4 Id
reviewLegAltId4	string	client review leg 4 alt Id
secKey5	OptionKey	leg 5
secType5	enum - secType5
mult5	ushort
side5	enum - side5
posType5	enum - posType5
ssaleFlag5	enum - ssaleFlag5
reviewLegId5	string	client review leg 5 Id
reviewLegAltId5	string	client review leg 5 alt Id
secKey6	OptionKey	leg 6
secType6	enum - secType6
mult6	ushort
side6	enum - side6
posType6	enum - posType6
ssaleFlag6	enum - ssaleFlag6
reviewLegId6	string	client review leg 6 Id
reviewLegAltId6	string	client review leg 6 alt Id

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRParentReviewResponse` (
    `parentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'SPDR order number',
    `sysEnvironment` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None' COMMENT 'original (source) sys environment [Stable, Current, etc]',
    `runStatus` ENUM('None','Prod','Beta','UAT','SysTest') NOT NULL DEFAULT 'None' COMMENT 'original (source) run status [Prod,Beta]',
    `parentShape` ENUM('None','Single','Cross','MLeg','MLegCross') NOT NULL DEFAULT 'None' COMMENT 'Parent Shape [Single, Cross, MLeg, MLegCross]',
    `altOrderId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate order ID (usually clOrdId from client)',
    `altPrevOrderId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate prev order ID (usually origClOrdId from client during cxl/replace)',
    `altAccnt` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) "long" account string (optional) [used to map between client and SR account strings]',
    `altUserName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'alternate (client assigned) user name (optional) [used to map between client and SR account strings]',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'Composite Security Key',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'Composite Security Key',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Composite Security Key',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'Composite Security Key',
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'Composite Security Key',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Security Type [Stock, Future, Option]',
    `securityDesc` TINYTEXT NOT NULL DEFAULT '' COMMENT 'additional security description',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR trading account',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR client firm',
    `spdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None' COMMENT 'from original parent order',
    `groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'from original parent order',
    `clientClOrdId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'client ClOrdId (if handled by ClientGateway)',
    `srcRoutingCode` VARCHAR(65) NOT NULL DEFAULT '' COMMENT 'RoutingCode (if handled by ClientGateway)',
    `strategy` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'client-supplied strategy string;visible on SpiderRock GUI tools and other order reports.',
    `userName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'name of the user entering the order',
    `orderSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None',
    `orderSize` INT NOT NULL DEFAULT 0 COMMENT 'maximum fill size (contracts) [can be less than requested]',
    `orderActiveSize` INT NOT NULL DEFAULT 0,
    `reviewResult` ENUM('None','Release','RejectParent','CancelParent','CancelReview','CancelReplaceReview') NOT NULL DEFAULT 'None',
    `reviewText` TINYTEXT NOT NULL DEFAULT '' COMMENT 'review release / reject text (will be showed to the user submitting the parent order)',
    `responseSpdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None' COMMENT 'source of the response (SRSE, FIX, etc)',
    `responseClOrdId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'review request / response clOrdID (if any)',
    `responseOrigClOrdId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'review request / response orig clOrdID',
    `responseCancelClOrdId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'review request / response cancel clOrdID',
    `firmType` ENUM('None','Customer','Firm','MarketMaker','ProCustomer','BrokerDealer','AwayMM','FirmJBO','BrkrDlrCust') NOT NULL DEFAULT 'None' COMMENT 'will override firmType on the original parent order (if != None)',
    `orderCapacity` ENUM('None','Agency','Principal','Individual','Proprietary','AgentOtherMember','RisklessPrincipal') NOT NULL DEFAULT 'None' COMMENT 'will override orderCapacity on the original parent order (if != None)',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None' COMMENT 'will override positionType on the original parent order (if != None)',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'will override ssaleFlag on the original parent order (if != None)',
    `locateQuan` INT NOT NULL DEFAULT 0 COMMENT 'locate quantity',
    `locateFirm` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'locate firm',
    `locatePool` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'locate pool @ firm granting the locate',
    `userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'will override value on original parent order (if != null)',
    `userData2` TINYTEXT NOT NULL DEFAULT '' COMMENT 'will override value on the original parent order (if != null)',
    `childData` TINYTEXT NOT NULL DEFAULT '' COMMENT 'will override value on the original parent order (if != null)',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `OrderLegsList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(OrderLegsList)),
    CONSTRAINT nonnegative_parentNumber CHECK(ASCII(parentNumber) < 56),
    CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
    PRIMARY KEY USING HASH (`parentNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpdrParentReviewResponse records are created when a parent order review request is completed';

SELECT TABLE EXAMPLE QUERY

SELECT
    `parentNumber`,
    `sysEnvironment`,
    `runStatus`,
    `parentShape`,
    `altOrderId`,
    `altPrevOrderId`,
    `altAccnt`,
    `altUserName`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `securityDesc`,
    `accnt`,
    `clientFirm`,
    `spdrSource`,
    `groupingCode`,
    `clientClOrdId`,
    `srcRoutingCode`,
    `strategy`,
    `userName`,
    `orderSide`,
    `orderSize`,
    `orderActiveSize`,
    `reviewResult`,
    `reviewText`,
    `responseSpdrSource`,
    `responseClOrdId`,
    `responseOrigClOrdId`,
    `responseCancelClOrdId`,
    `firmType`,
    `orderCapacity`,
    `positionType`,
    `ssaleFlag`,
    `locateQuan`,
    `locateFirm`,
    `locatePool`,
    `userData1`,
    `userData2`,
    `childData`,
    `timestamp`,
    `OrderLegsList`
FROM `SRTrade`.`MsgSRParentReviewResponse`
WHERE 
    /* Replace with a CHAR(19) */
    `parentNumber` = 'Example_parentNumber';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SpdrParentReviewResponse' ORDER BY ordinal_position ASC;